﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace ECCIT.Data
{
    internal class OracleHelper : BaseDBHelper
    {
        /// <summary>
        ///     Build database connection string via related arguments
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="dataSource"></param>
        /// <param name="dbName"></param>
        /// <param name="dbUser"></param>
        /// <param name="dbPassword"></param>
        /// <returns></returns>
        public override string BuildConnectionString(string dataSource, string dbName, string dbUser, string dbPassword)
        {
            OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder();
            builder.Add("Data Source", dataSource);
            builder.Add("User ID", dbUser);
            builder.Add("Password", dbPassword);

            return builder.ConnectionString;
        }

        public override IDbConnection GetDbConnection(string connString)
        {
            IDbConnection conn = new OracleConnection(connString);
            return conn;
        }

        public override IDbCommand GetDbCommand()
        {
            IDbCommand cmd = new OracleCommand();
            return cmd;
        }

        public override IDataAdapter GetDataAdapter(IDbCommand command)
        {
            IDataAdapter adapter = new OracleDataAdapter((OracleCommand)command);
            return adapter;
        }

        public override IDataParameter GetDataParameter(string parameterName, DataType dataType)
        {
            int num1 = GetDataLengthByDataType(dataType);
            return GetDataParameter(parameterName, dataType, num1);
        }

        public override IDataParameter GetDataParameter(string parameterName, DataType dataType, int parameterSize)
        {
            IDataParameter parameter1 = GetDbParameter(parameterName);
            ((OracleParameter)parameter1).OracleType = GetOracleTypeByDataType(dataType);

            int num1 = GetDataLengthByDataType(dataType);
            if (num1 == -1)
                num1 = parameterSize;
            ((OracleParameter)parameter1).Size = num1;

            return parameter1;
        }

        public override IDataParameter GetDataParameter(string parameterName, DataType dataType, int parameterPrecision, int parameterScale)
        {
            IDataParameter parameter1 = GetDbParameter(parameterName);
            ((OracleParameter)parameter1).OracleType = GetOracleTypeByDataType(dataType);
            ((OracleParameter)parameter1).Precision = Convert.ToByte(parameterPrecision);
            ((OracleParameter)parameter1).Scale = Convert.ToByte(parameterScale);
            return parameter1;
        }

        public override IDataParameter GetDbParameter(string parameterName)
        {
            return new OracleParameter(":" + parameterName, null);
        }

        public override bool TableExists(string tableName)
        {
            string cmdText = "SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME='" + tableName + "'";
            object result = DBHelper.ExecuteScalar(cmdText);
            if (result != null && result != DBNull.Value)
                return true;

            return false;
        }

        public override ResultInfo CreateTable(string tableName, List<Column> columns)
        {
            //StringBuilder builder = new StringBuilder("CREATE TABLE ").Append(tableName).Append(" (");
            //int i = 0;
            //string primaryKey = string.Empty;
            //foreach (Column column in columns)
            //{
            //    string fieldType = fieldInfo.Type;
            //    SystemField systemField = new SystemField();
            //    SystemFieldInfo systemFieldInfo = systemField.GetSysFieldInfo(fieldType);

            //    builder.Append(fieldInfo.PhysicalName).Append(" ").Append(column.);
            //    if (systemFieldInfo.LengthEditable == "1")
            //    {
            //        builder.Append("(").Append(fieldInfo.Length);
            //        if (systemFieldInfo.ScaleEditable == "1")
            //        {
            //            builder.Append(",").Append(fieldInfo.Scale);
            //        }
            //        builder.Append(") ");
            //    }

            //    if (fieldInfo.PhysicalName.ToUpper() == "GUID")
            //    {
            //        primaryKey += (primaryKey == string.Empty) ? fieldInfo.PhysicalName : "," + fieldInfo.PhysicalName;
            //    }

            //    if (fieldInfo.IsNullable == "0")
            //    {
            //        builder.Append(" NOT NULL");
            //    }
            //    else
            //    {
            //        builder.Append(" NULL");
            //    }

            //    builder.Append(",");

            //    if (i == fields.Count - 1 && primaryKey != string.Empty)
            //    {
            //        builder.Append(" CONSTRAINT PK_").Append(tableInfo.PhysicalName).Append(" PRIMARY KEY (").Append(primaryKey).Append(") ");
            //    }

            //    i++;
            //}

            //builder.Append(")");

            //if (CheckTableCreated(guid))
            //    Drop(guid);

            //string cmdCreate = builder.ToString();
            //int rowsAffected = DBHelper.ExecuteNonQuery(cmdCreate);

            //return (rowsAffected == -1) ? true : false;

            //return false;
            return new ResultInfo(true);
        }

        private OracleType GetOracleTypeByDataType(DataType dataType)
        {
            switch (dataType)
            {
                case DataType.String:
                        return OracleType.NVarChar;
                case DataType.Int:
                        return OracleType.Number;
                case DataType.Decimal:
                        return OracleType.Float;
                case DataType.NText:
                        return OracleType.Clob;
                case DataType.Date:
                        return OracleType.DateTime;
                case DataType.Time:
                        return OracleType.Timestamp;
                case DataType.DateTime:
                        return OracleType.DateTime;
                case DataType.YearMonth:
                        return OracleType.VarChar;
                case DataType.Binary:
                        return OracleType.Blob;
                case DataType.BigInt:
                    return OracleType.Int32;    // here is an issue: DataType.BigInt actually stands for System.Int64;
                case DataType.Bit:
                    return OracleType.Number;
                case DataType.SmallInt:
                    return OracleType.UInt16;
                case DataType.TinyInt:
                    return OracleType.SByte;
            }
            return OracleType.NVarChar;
        }

        internal class DBFunction : BaseDBHelper.BaseDBFunction
        {
            public override string Length(string columnName)
            {
                return " LENGTH(" + columnName + ") ";
            }

            public override string Substring(string columnName, int startIndex, int? length)
            {
                string ret = " Mid(" + columnName + "," + startIndex;
                if (length != null)
                    ret += "," + length;
                ret += ") ";
                return ret;
            }

            public override string IsNull(string columnName, object replacementValue)
            {
                string ret = " NVL(" + columnName + ",'" + replacementValue + "') ";
                return ret;
            }

            /// <summary>
            ///     该方法目前等同于SQLSERVER中的方法，如果在oracle上执行，需要进行测试
            /// </summary>
            /// <param name="columnName"></param>
            /// <param name="value"></param>
            /// <returns></returns>
            public override string BooleanEquals(string columnName, bool value)
            {
                string ret = " " + columnName + "='" + value + "' ";
                return ret;
            }
        }
    }
}
